package my.home;

import java.sql.*;
import java.util.ArrayList;
import java.util.Random;

import javax.sql.*;
import javax.naming.*;
import my.home.*;
import java.sql.Timestamp;

public class meetingDB{
	private static meetingDB instance = new meetingDB();
	
	public static meetingDB getInstance(){
		return instance;
	}
	
	private meetingDB(){}
	
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)envCtx.lookup("jdbc/mp");
		return ds.getConnection();
	}
	
	
	public boolean insertMember(meetingDataBean member) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("insert into meeting(m_title,m_password,m_location,m_description,m_createtime,m_initiatorAlias,m_initiatorEmail,poll_time,poll_id)"
					+ "values(?,?,?,?,now(),?,?,?,?)");
			pstmt.setString(1, member.getM_title());
			pstmt.setString(2, member.getM_password());
			pstmt.setString(3, member.getM_location());
			pstmt.setString(4, member.getM_description());
			pstmt.setString(5, member.getM_initiatorAlias());
			pstmt.setString(6, member.getM_initiatorEmail());
			pstmt.setString(7, member.getPoll_time());
			pstmt.setString(8, member.getPoll_id());
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
				ex.printStackTrace();
				result = false;
		}finally{
				if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null)try{conn.close();}catch(SQLException ex){}
		}return result;
	}
	
	
	public meetingDataBean getMember(String poll_id) throws Exception{ 
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		meetingDataBean bi = new meetingDataBean();
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select * from meeting where poll_id = ?");
			pstmt.setString(1, poll_id);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
			bi.setM_title(rs.getString("m_title"));
			bi.setM_password(rs.getString("m_password"));
			bi.setM_location(rs.getString("m_location"));
			bi.setM_description(rs.getString("m_description"));
			bi.setM_createtime(rs.getString("m_createtime"));
			bi.setM_initiatorAlias(rs.getString("m_initiatorAlias"));
			bi.setM_initiatorEmail(rs.getString("m_initiatorEmail"));
			bi.setPoll_time(rs.getString("poll_time"));
			}
		}catch(Exception ex){
				ex.printStackTrace();
		}finally{
			    if(rs != null)try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null)try{conn.close();}catch(SQLException ex){}
		}
		return bi;
	}
	
	public boolean check_member(String poll_id) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result = false;
		String check="";
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select poll_id from meeting where poll_id = ?");
			pstmt.setString(1, poll_id);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				check = rs.getString("poll_id");
			}else check="false";
			if(poll_id.equals(check)) result = true; 
		}catch(Exception ex){
				ex.printStackTrace();
		}finally{
			    if(rs != null)try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null)try{conn.close();}catch(SQLException ex){}
		}
		return result;
		}
	
		public boolean updateMember(String poll_id, String poll_time) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("update meeting set poll_time=?"+"where poll_id=?");
			pstmt.setString(1, poll_time);
			pstmt.setString(2, poll_id);
			
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
			result = false;
		}finally{
				if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null)try{conn.close();}catch(SQLException ex){}
		}
		return result;
	}
}

	
